package com.uniview.service.report.impl;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.uniview.entity.vo.Pager;
import com.uniview.entity.vo.PersonOrderReport;
import com.uniview.plugins.adapter.OLDataSource;
import com.uniview.service.report.PersonOrderReportService;

import javax.annotation.Resource;

@Service("personOrderReoprtService")
public class PersonOrderReportServiceImpl implements PersonOrderReportService {

	@Autowired
	private OLDataSource olDataSource;

	@Resource
	private SessionFactory sessionFactory;

	@Override
	public Pager<PersonOrderReport> findAllPersonOrderReport(String handler, String area, String startTime,
			String endTime, int page, int limit) {

		StringBuilder builder = new StringBuilder();
		StringBuilder query = new StringBuilder();
		builder.append("select * from (select A.*,ROWNUM as id FROM ( ");
		builder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		builder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction, count(decode(sd.iswarning, 1, 1, null)) as warnNum ");
		builder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 ");

		if (StringUtils.isNotBlank(handler)) {
			builder.append(" and sd.handle like '%" + handler + "%' ");
			query.append(" and sd.handle like '%" + handler + "%' ");

		}

		if (StringUtils.isNotBlank(area)) {
			builder.append(" and sd.handlerDept like '%" + area + "%' ");
			query.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			builder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
			query.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}

		builder.append("  GROUP BY sd.handle) A WHERE ROWNUM <= " + page + " * " + limit + " ) WHERE id > (" + page
				+ " - 1) *" + limit);
		System.out.println("SQL : " + builder.toString());
		StringBuilder countBuilder = new StringBuilder();

		countBuilder.append("select count(*) from (");
		countBuilder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		countBuilder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction, count(decode(sd.iswarning, 1, 1, null)) as warnNum ");
		countBuilder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 ");
		if (StringUtils.isNotBlank(handler)) {
			countBuilder.append(" and sd.handle like '%" + handler + "%' ");
		}

		if (StringUtils.isNotBlank(area)) {
			countBuilder.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			countBuilder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('"
					+ endTime + " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		countBuilder.append("  GROUP BY sd.handle) ");
		List<PersonOrderReport> list = olDataSource.queryForList(PersonOrderReport.class, builder.toString());
		int total = olDataSource.getCount(countBuilder.toString());
		// 统计每个人满意数
		String sql = "select sd.handle, count(sd.satisfaction) num from SD_ORDERS sd where sd.status <> -99 and sd.satisfaction=1 " + query
				+ " group by sd.handle ";
		List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Map<String, Object> map = new HashMap<>();
		for (int i = 0; i < datas.size(); i++) {
			Map<String, Object> map1 = (Map<String, Object>) datas.get(i);
			map.put(map1.get("HANDLE").toString(), map1.get("NUM"));
		}
		for (PersonOrderReport report : list) {
			String num = String.valueOf(map.get(report.getHandle()));
			if (!"null".equals(num)) {
				double f = new BigDecimal(Float.parseFloat(num) / report.getSumOrders())
						.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
				report.setSatisfaction((float) f);
			} else {
				report.setSatisfaction((float) 0.0);
			}
		}
		Pager<PersonOrderReport> pager = new Pager<PersonOrderReport>();
		pager.setPagerList(list);
		pager.setTotalRow(total);

		return pager;
	}

	@Override
	public List<PersonOrderReport> findAllPersonOrderReport(String handler, String area, String startTime,
			String endTime) {
		StringBuilder builder = new StringBuilder();
		StringBuilder query = new StringBuilder();
		builder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		builder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction, count(decode(sd.iswarning, 1, 1, null)) as warnNum  ");
		builder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 ");

		if (StringUtils.isNotBlank(handler)) {
			builder.append(" and sd.handle like '%" + handler + "%' ");
			query.append(" and sd.handle like '%" + handler + "%' ");

		}

		if (StringUtils.isNotBlank(area)) {
			builder.append(" and sd.handlerDept like '%" + area + "%' ");
			query.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			builder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
			query.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}

		builder.append("  GROUP BY sd.handle ");
		System.out.println("SQL : " + builder.toString());

		List<PersonOrderReport> list = olDataSource.queryForList(PersonOrderReport.class, builder.toString());
		// 统计每个人满意数
		String sql = "select sd.handle,count(sd.satisfaction) num from SD_ORDERS  sd where sd.status <> -99 and sd.satisfaction=1 " + query
				+ " group by sd.handle ";
		List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Map<String, Object> map = new HashMap<>();
		for (int i = 0; i < datas.size(); i++) {
			Map<String, Object> map1 = (Map<String, Object>) datas.get(i);
			map.put(map1.get("HANDLE").toString(), map1.get("NUM"));
		}
		for (PersonOrderReport report : list) {
			String num = String.valueOf(map.get(report.getHandle()));
			if (!"null".equals(num)) {
				double f = new BigDecimal(Float.parseFloat(num) / report.getSumOrders())
						.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
				report.setSatisfaction((float) f);
			}
		}
		return list;
	}

	@Override
	public Pager<PersonOrderReport> findAllSatisfactionReoprt(String handler, String area, String startTime,
			String endTime, int page, int limit) {
		StringBuilder builder = new StringBuilder();
		StringBuilder query = new StringBuilder();
		builder.append("select * from (select A.*,ROWNUM as id FROM ( ");
		builder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		builder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction ");
		builder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 and sd.satisfaction < 2");

		if (StringUtils.isNotBlank(handler)) {
			builder.append(" and sd.handle like '%" + handler + "%' ");
			query.append(" and sd.handle like '%" + handler + "%' ");

		}

		if (StringUtils.isNotBlank(area)) {
			builder.append(" and sd.handlerDept like '%" + area + "%' ");
			query.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			builder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
			query.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}

		builder.append("  GROUP BY sd.handle) A WHERE ROWNUM <= " + page + " * " + limit + " ) WHERE id > (" + page
				+ " - 1) *" + limit);
		System.out.println("SQL : " + builder.toString());
		StringBuilder countBuilder = new StringBuilder();

		countBuilder.append("select count(*) from (");
		countBuilder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		countBuilder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction ");
		countBuilder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 and sd.satisfaction < 2");
		if (StringUtils.isNotBlank(handler)) {
			countBuilder.append(" and sd.handle like '%" + handler + "%' ");
		}

		if (StringUtils.isNotBlank(area)) {
			countBuilder.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			countBuilder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('"
					+ endTime + " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}
		countBuilder.append("  GROUP BY sd.handle) ");
		List<PersonOrderReport> list = olDataSource.queryForList(PersonOrderReport.class, builder.toString());
		int total = olDataSource.getCount(countBuilder.toString());
		// 统计每个人满意数
		String sql = "select sd.handle, count(sd.satisfaction) num from SD_ORDERS sd where sd.status <> -99 and sd.satisfaction=1 " + query
				+ " group by sd.handle ";
		List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Map<String, Object> map = new HashMap<>();
		for (int i = 0; i < datas.size(); i++) {
			Map<String, Object> map1 = (Map<String, Object>) datas.get(i);
			map.put(map1.get("HANDLE").toString(), map1.get("NUM"));
		}
		for (PersonOrderReport report : list) {
			String num = String.valueOf(map.get(report.getHandle()));
			if (!"null".equals(num)) {
				double f = new BigDecimal(Float.parseFloat(num) / report.getSumOrders())
						.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
				report.setSatisfaction((float) f);
			} else {
				report.setSatisfaction((float) 0.0);
			}
		}
		Pager<PersonOrderReport> pager = new Pager<PersonOrderReport>();
		pager.setPagerList(list);
		pager.setTotalRow(total);

		return pager;
	}

	@Override
	public List<PersonOrderReport> findAllSatisfactionReoprt(String handler, String area, String startTime,
			String endTime) {
		StringBuilder builder = new StringBuilder();
		StringBuilder query = new StringBuilder();
		builder.append(
				"select max(sd.handlerDept) as area,max(sd.handle) as handle,max(sd.handleNo) as handleNo,sum(sd.workhours) as sumHours , ");
		builder.append("count(sd.handle) as sumOrders ,max(sd.satisfaction) as satisfaction ");
		builder.append("from SD_ORDERS sd where 1=1 and sd.status <> -99 and sd.satisfaction < 2 ");

		if (StringUtils.isNotBlank(handler)) {
			builder.append(" and sd.handle like '%" + handler + "%' ");
			query.append(" and sd.handle like '%" + handler + "%' ");

		}

		if (StringUtils.isNotBlank(area)) {
			builder.append(" and sd.handlerDept like '%" + area + "%' ");
			query.append(" and sd.handlerDept like '%" + area + "%' ");
		}

		if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
			builder.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
			query.append(" and sd.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
					+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ");
		}

		builder.append("  GROUP BY sd.handle ");
		System.out.println("SQL : " + builder.toString());

		List<PersonOrderReport> list = olDataSource.queryForList(PersonOrderReport.class, builder.toString());
		// 统计每个人满意数
		String sql = "select sd.handle,count(sd.satisfaction) num from SD_ORDERS  sd where sd.satisfaction=1 and sd.status <> -99 and sd.satisfaction < 2 " + query
				+ " group by sd.handle ";
		List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		Map<String, Object> map = new HashMap<>();
		for (int i = 0; i < datas.size(); i++) {
			Map<String, Object> map1 = (Map<String, Object>) datas.get(i);
			map.put(map1.get("HANDLE").toString(), map1.get("NUM"));
		}
		for (PersonOrderReport report : list) {
			String num = String.valueOf(map.get(report.getHandle()));
			if (!"null".equals(num)) {
				double f = new BigDecimal(Float.parseFloat(num) / report.getSumOrders())
						.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
				report.setSatisfaction((float) f);
			} else {
				report.setSatisfaction((float) 0.0);
			}
		}
		return list;
	}

}
